package com.liuzhou.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.liuzhou.dao.SubTaskDao;
import com.liuzhou.model.Subtask;


import com.liuzhou.util.C3P0Util;
/**
 * 
 * @author simon
 *
 */
public class SubtaskDaoImpl implements SubTaskDao {

	@Override
	public List<Subtask> queryByItemId(Integer itemId) throws SQLException {
		Subtask subtask = null;
		List<Subtask> list = new ArrayList<Subtask>();
		Connection conn = null;
		try {
			conn = C3P0Util.getConnection();
			String sql = " select * from `t_subtask`"
						+ " where item_id = ? ";			
			PreparedStatement ps = conn.prepareStatement(sql);			
			ps.setInt(1, itemId);		
			
			ResultSet rs = ps.executeQuery();
			
			if( rs.next() ){
				subtask = new Subtask();
				subtask.setId( rs.getInt("id") );
				subtask.setItem_id( rs.getInt("item_id") );
				subtask.setName(rs.getString("name"));
				subtask.setTask_detail(rs.getString("task_detail"));
				list.add(subtask);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}finally{
			C3P0Util.close(conn);
		}
		return list;
	}

	@Override
	public Subtask queryBySubtaskId(Integer subId) throws SQLException {
		Subtask subtask = null;
		Connection conn = null;
		try {
			conn = C3P0Util.getConnection();
			String sql = " select * from `t_subtask`"
						+ " where id = ? ";			
			PreparedStatement ps = conn.prepareStatement(sql);			
			ps.setInt(1, subId);		
			
			ResultSet rs = ps.executeQuery();
			
			if( rs.next() ){
				subtask = new Subtask();
				subtask.setId( rs.getInt("id") );
				subtask.setItem_id( rs.getInt("item_id") );
				subtask.setName(rs.getString("name"));
				subtask.setTask_detail(rs.getString("task_detail"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}finally{
			C3P0Util.close(conn);
		}
		return subtask;
	}

	@Override
	public void addSubtask(Integer itemId, String name, String task_detail) throws SQLException {
		System.out.println("Dao收到参数：itemId:"+itemId+" name:"+name+" task_detail:"+task_detail);
		Connection conn = null;
		try {
			conn = C3P0Util.getConnection();
			String sql = "insert into `t_subtask`(item_id, name, task_detail)"
						+" values( ? , ? , ?)	";	
			PreparedStatement ps = conn.prepareStatement(sql);			
			ps.setInt(1, itemId);	
			ps.setString(2, name);
			ps.setString(3, task_detail);
			
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;			
		}finally{
			C3P0Util.close(conn);
		}
	}


	@Override
	public void deleteSubtaskById(Integer subId) throws SQLException {
		Connection conn = null;
		try {
			conn = C3P0Util.getConnection();
			String sql ="delete from `t_subtask`  where id =?";	
			PreparedStatement ps = conn.prepareStatement(sql);			
			ps.setInt(1, subId);	
			
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;			
		}finally{
			C3P0Util.close(conn);
		}
	}

	@Override
	public Integer countNum() throws SQLException {
		Connection conn = null;
		Integer totalRow = 0;
		try {
			conn = C3P0Util.getConnection();
			String sql ="select count(*) totalRow from `t_subtask` sub";	
			PreparedStatement ps = conn.prepareStatement(sql);			
			
			
			ResultSet rs = ps.executeQuery();
			if( rs.next() )totalRow = rs.getInt("totalRow");

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;			
		}finally{
			C3P0Util.close(conn);
		}
		return totalRow;
	}

	@Override
	public List<Subtask> queryAll(Integer pageNum, Integer pageRow) throws SQLException {
		Connection conn = null;
		List<Subtask> list = new ArrayList<Subtask>();
		Subtask subtask = null;
		try {
			conn = C3P0Util.getConnection();
			//子任务表 sub 和父事项表 item 联合查询子任务表的所有字段和父事项的名称 Iname
			//按子任务表的Id升序排列
			String sql = " select sub.*, item.name Iname "
					+" from `t_subtask` sub "
					+" left join `t_item` item "
					+" on sub.item_id = item.id "
					+" order by sub.id asc "
					+" limit ? , ? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, (pageNum - 1) * pageRow);
			ps.setInt(2, pageRow);
			
			ResultSet rs = ps.executeQuery();
			while( rs.next() ){
				subtask = new Subtask();
				subtask.setId( rs.getInt("id") );
				subtask.setName( rs.getString("name") );
				subtask.setTask_detail( rs.getString("task_detail") );
				subtask.setItem_name(rs.getString("Iname"));
				list.add(subtask);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}finally{
			C3P0Util.close(conn);
		}
		return list;
	}

	@Override
	public void update(Integer id,Integer item_id, String name, String task_detail)
			throws SQLException {
		Connection conn = null;
		try {
			conn = C3P0Util.getConnection();
			String sql =" update `t_subtask` "
					+" set item_id =?,"
					+" name = ?,"
					 +"task_detail = ?"
					+"where id = ?";	
			PreparedStatement ps = conn.prepareStatement(sql);			
			ps.setInt(1, item_id);	
			ps.setString(2, name);
			ps.setString(3, task_detail);
			ps.setInt(4, id);
			
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;			
		}finally{
			C3P0Util.close(conn);
		}
		
	}
	
	
	
}
